/* $Id: AppMetaDataDAOImpl.java 
 * Copyright (C) 2011, TRAKHEES
 *  
 * Revision History
 *
 * Date  			Author 		    	Remarks
 * ---------------------------------------------------------------------------------
 *
 */
package ae.pcfc.etks.online.common.dao.provider;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.annotations.NamedNativeQuery;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.criterion.Subqueries;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StringType;
import org.hibernate.type.Type;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

import ae.pcfc.etks.online.common.ConstantsCommon;
import ae.pcfc.etks.online.common.ConstantsCommon.RequestType;
import ae.pcfc.etks.online.common.dao.AppMetaDataDAO;
import ae.pcfc.etks.online.common.domain.AppMetaData;
import ae.pcfc.etks.online.common.domain.ClientCompanyReq;
import ae.pcfc.etks.online.common.domain.Service;
import ae.pcfc.etks.online.common.domain.ServiceReq;
import ae.pcfc.etks.online.common.domain.ServiceRole;
import ae.pcfc.etks.online.common.vo.ActivityVO;
import ae.pcfc.etks.online.common.vo.AppMetaDataDetailVO;
import ae.pcfc.etks.online.common.vo.LabelVO;
import ae.pcfc.etks.online.common.vo.WorkflowRequestSearchCriteriaVO;
import ae.pcfc.etks.online.common.vo.WorkflowRequestVO;


@Repository
public class AppMetaDataDAOImpl extends BaseDAO implements AppMetaDataDAO{

	/**
	 * add
	 * $
	 * @param appMetaData
	 * @see ae.pcfc.etks.online.common.dao.AppMetaDataDAO#add(ae.pcfc.etks.online.common.domain.AppMetaData)
	 * @return
	 */
	public void add(AppMetaData appMetaData) {
		
		//Object incidentId = getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery("SELECT SQ_ETKS_WF_INCIDENT.nextval from DUAL").uniqueResult();
		Object refNo = getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery("SELECT SQ_ETKS_CC_REQ_REF.nextval from DUAL").uniqueResult();
		
		//appMetaData.setWfIncidentId(incidentId.toString());
		
		if(appMetaData.getClientRefNo() != null && RequestType.getRequestTypefromString(appMetaData.getClientRefNo()) != null)
		{
			String clientRefNo = appMetaData.getClientRefNo();
			appMetaData.setClientRefNo(clientRefNo.concat("-"+refNo.toString()));
		}
		
		getHibernateTemplate().getSessionFactory().getCurrentSession().save(appMetaData);
	}

	/**
	 * findByPrimaryKey
	 * $
	 * @param id
	 * @return
	 * @see ae.pcfc.etks.online.common.dao.AppMetaDataDAO#findByPrimaryKey(java.lang.String)
	 * @return
	 */
	public AppMetaData findByPrimaryKey(String id) {
	
		Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(AppMetaData.class, "appMetaData");
		criteria.add(Restrictions.eq("appMetaData.applicationId", id));
			
		@SuppressWarnings("unchecked")		
		List<AppMetaData> list =  criteria.list();
		
		if(list != null && list.size() > 0)
			return list.get(0);
		else
			return null;
		
	}

	/**
	 * findByPrimaryKeyProcessAndStepCode
	 * $
	 * @param id
	 * @param processCode
	 * @param processStepCode
	 * @return
	 * @see ae.pcfc.etks.online.common.dao.AppMetaDataDAO#findByPrimaryKeyProcessAndStepCode(java.lang.String, java.lang.String, java.lang.String)
	 * @return
	 */
	public AppMetaData findByPrimaryKeyProcessAndStepCode(String id,
			String processCode, String processStepCode) {
		
		Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(AppMetaData.class, "appMetaData");
		criteria.add(Restrictions.eq("appMetaData.applicationId", id));
		criteria.createAlias("appMetaData.process", "process");
		criteria.createAlias("process.processSteps", "processStep");
		criteria.add(Restrictions.eq("process.processCode", processCode));
		criteria.add(Restrictions.eq("processStep.processStepCode", processStepCode));		
		criteria.add(Restrictions.eq("processStep.processStepStatus", ConstantsCommon.ACTIVE));
		@SuppressWarnings("unchecked")		
		List<AppMetaData> list =  criteria.list();
		
		if(list != null && list.size() > 0)
			return list.get(0);
		else
			return null;
	}

	/**
	 * update
	 * $
	 * @param appMetaData
	 * @see ae.pcfc.etks.online.common.dao.AppMetaDataDAO#update(ae.pcfc.etks.online.common.domain.AppMetaData)
	 * @return
	 */
	public void update(AppMetaData appMetaData) {
		
		getHibernateTemplate().getSessionFactory().getCurrentSession().saveOrUpdate(appMetaData);
		
	}
	
	/**
	 * getNextApplicationId
	 * $
	 * @return
	 * @see ae.pcfc.etks.online.common.dao.AppMetaDataDAO#getNextApplicationId()
	 * @return
	 */
	public String getNextApplicationId(){
		
		Object applicationId = getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery("SELECT SQ_ETKS_APP_ID.nextval from DUAL").uniqueResult();
		
		if(applicationId != null)
		{
			return ((BigDecimal)applicationId).toString();
		}
		return null;
	}

	/**
	 * findAppMetaDataByUserId
	 * $
	 * @param id
	 * @return
	 * @see ae.pcfc.etks.online.common.dao.AppMetaDataDAO#findAppMetaDataByUserId(java.lang.Long)
	 * @return
	 */
	public List<AppMetaData> findAppMetaDataByUserId(Long id) {
		
		Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(AppMetaData.class, "appMetaData");
		criteria.add(Restrictions.eq("appMetaData.userId", id));
		criteria.addOrder(Order.desc("appMetaData.applicationDate"));	
		@SuppressWarnings("unchecked")		
		List<AppMetaData> list =  criteria.list();
		
		if( list != null && list.size() > 0)
			return list;
		else
			return null;
	}

	/**
	 * findByClientRefNo
	 * $
	 * @param refNo
	 * @return
	 * @see ae.pcfc.etks.online.common.dao.AppMetaDataDAO#findByClientRefNo(java.lang.String)
	 * @return
	 */
	public AppMetaData findByClientRefNo(String refNo) {
		
		Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(AppMetaData.class, "appMetaData");
		criteria.add(Restrictions.like("appMetaData.clientRefNo", refNo));
			
		@SuppressWarnings("unchecked")		
		List<AppMetaData> list =  criteria.list();
		
		if(list != null && list.size() > 0)
			return list.get(0);
		else
			return null;
	}

	/**
	 * findCompletedAppMetaDataByUserId
	 * $
	 * @param id
	 * @return
	 * @see ae.pcfc.etks.online.common.dao.AppMetaDataDAO#findCompletedAppMetaDataByUserId(java.lang.Long)
	 * @return
	 */
	public List<AppMetaData> findCompletedAppMetaDataByUserId(Long id) {
		
		Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(AppMetaData.class, "appMetaData");
		criteria.add(Restrictions.eq("appMetaData.userId", id));
		criteria.addOrder(Order.desc("appMetaData.applicationDate"));
		criteria.add(Restrictions.ne("wfStatus", ConstantsCommon.IN_PROGRESS));
		@SuppressWarnings("unchecked")		
		List<AppMetaData> list =  criteria.list();
		
		if( list != null && list.size() > 0)
			return list;
		else
			return null;
	}
	
	/**
	 * findAppMetaDataByExample
	 * $
	 * @param appMetaDataDetailVO
	 * @return
	 * @see ae.pcfc.etks.online.common.dao.AppMetaDataDAO#findAppMetaDataByExample(ae.pcfc.etks.online.common.vo.AppMetaDataDetailVO)
	 * @return
	 */
	public List<AppMetaData> findAppMetaDataByExample(AppMetaDataDetailVO appMetaDataDetailVO) {
		
		Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(AppMetaData.class, "appMetaData");
		criteria.createAlias("appMetaData.process", "process", Criteria.LEFT_JOIN);	
		if(appMetaDataDetailVO.getlUserId()!= null){
			criteria.add(Restrictions.eq("appMetaData.userId", appMetaDataDetailVO.getlUserId()));
		}
		if(appMetaDataDetailVO.getStrApplicationId()!= null){
			criteria.add(Restrictions.eq("appMetaData.applicationId", appMetaDataDetailVO.getStrApplicationId()));
		}
		if(appMetaDataDetailVO.getApplicationLink()!= null){
			criteria.add(Restrictions.eq("appMetaData.applicationLink", appMetaDataDetailVO.getApplicationLink()));
		}	
		if(appMetaDataDetailVO.getStrStatusDescAr()!= null){
			criteria.add(Restrictions.eq("appMetaData.wfStatusAr", appMetaDataDetailVO.getStrStatusDescAr()));
		}
		if(appMetaDataDetailVO.getStrStatusDescEn()!= null){
			criteria.add(Restrictions.eq("appMetaData.wfStatusEn", appMetaDataDetailVO.getStrStatusDescEn()));
		}
		if(appMetaDataDetailVO.getStrWfStatus()!= null){
			criteria.add(Restrictions.eq("appMetaData.wfStatus", appMetaDataDetailVO.getStrWfStatus()).ignoreCase());
		}
		if(appMetaDataDetailVO.getStrWFIncidentId()!= null){
			criteria.add(Restrictions.eq("appMetaData.wfIncidentId", appMetaDataDetailVO.getStrWFIncidentId()));
		}
		if(appMetaDataDetailVO.getStrWFinitatedBy()!= null){
			criteria.add(Restrictions.eq("appMetaData.wfInitiatedBy", appMetaDataDetailVO.getStrWFinitatedBy()).ignoreCase());
		}
		if(appMetaDataDetailVO.getStrProcessCodeDescAr()!= null){
			criteria.add(Restrictions.eq("process.processDescA", appMetaDataDetailVO.getStrProcessCodeDescAr()));
		}
		if(appMetaDataDetailVO.getStrProcessCodeDescEn()!= null){
			criteria.add(Restrictions.eq("process.processDesc", appMetaDataDetailVO.getStrProcessCodeDescEn()));
		}		
		if(appMetaDataDetailVO.getlClientId()!= null){
			criteria.add(Restrictions.eq("appMetaData.clientId", appMetaDataDetailVO.getlClientId()));
		}
		if(appMetaDataDetailVO.getProcessCode()!= null){
			criteria.add(Restrictions.eq("process.processCode", appMetaDataDetailVO.getProcessCode().getProcessCode()));
		}
		if(appMetaDataDetailVO.getStrClientRefNo()!= null){
			criteria.add(Restrictions.eq("appMetaData.clientRefNo", appMetaDataDetailVO.getStrClientRefNo()).ignoreCase());
		}
		if(appMetaDataDetailVO.getApplicationDate()!= null){
			criteria.add(Restrictions.eq("appMetaData.applicationDate", appMetaDataDetailVO.getApplicationDate()));			
		}
		criteria.addOrder(Order.desc("appMetaData.applicationDate"));
		@SuppressWarnings("unchecked")		
		List<AppMetaData> list =  criteria.list();
		
		if( list != null && list.size() > 0)
			return list;
		else
			return null;
	}

	/**
	 * updateStatus
	 * $
	 * @param applicationId
	 * @param statusEn
	 * @param statusAr
	 * @see ae.pcfc.etks.online.common.dao.AppMetaDataDAO#updateStatus(java.lang.String, java.lang.String, java.lang.String)
	 * @return
	 */
	public void updateStatus(String applicationId, String statusEn,String statusAr) {
		AppMetaData appMetaData = findByPrimaryKey(applicationId);
		if(appMetaData != null){
			appMetaData.setWfStatusEn(statusEn);
			appMetaData.setWfStatusAr(statusAr);
			update(appMetaData);
		}
	}

	/**
	 * findAppMetaDataByUserName
	 * $
	 * @param strUserName
	 * @return
	 * @see ae.pcfc.etks.online.common.dao.AppMetaDataDAO#findAppMetaDataByUserName(java.lang.String)
	 * @return
	 */
	public List<AppMetaData> findAppMetaDataByUserName(String strUserName) {
		Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(AppMetaData.class, "appMetaData");
		criteria.add(Restrictions.eq("appMetaData.wfInitiatedBy", strUserName).ignoreCase());
		criteria.addOrder(Order.desc("appMetaData.applicationDate"));	
		@SuppressWarnings("unchecked")		
		List<AppMetaData> list =  criteria.list();
		
		if( list != null && list.size() > 0)
			return list;
		else
			return null;
	}
	
	public List<WorkflowRequestVO> findWorkflowRequests(final WorkflowRequestSearchCriteriaVO searchCriteria) {
		if(searchCriteria != null){
			StringBuffer query = new StringBuffer(" SELECT " +
			" AMD.APPLICATION_ID AS strApplicationId, " +
			" AMD.WF_INCIDENT_ID AS strWFIncidentId, " +
			" AMD.CLIENT_REF_NO AS strClientRefNo, " +
			" AMD.PROCESS_CODE AS strProcessCode, " +
			" P.PROCESS_DESC AS strProcessCodeDescEn, " +
			" P.PROCESS_DESC_A AS strProcessCodeDescAr, " +
			" AMD.WF_STATUS_EN AS strStatusDescEn, " +
			" AMD.WF_STATUS_AR AS strStatusDescAr, " +
			" AMD.APPLICATION_LINK AS strApplicationLink" +
			" FROM " +
			" T_ETKS_APP_META_DATA AMD, T_ETKS_CLIENT_COMPANY_REQ NCCR, " +
			" T_ETKS_CLIENT_PERSON_REQ NCPR, T_ETKS_USER_REQ CAR, " +
			" T_ETKS_CLIENT CLNT , T_ETKS_PROCESS P" +
			" WHERE " +
			" AMD.APPLICATION_ID = NCCR.APPLICATION_ID(+) AND " +
			" AMD.APPLICATION_ID = NCPR.APPLICATION_ID(+) AND " +
			" AMD.APPLICATION_ID = CAR.APPLICATION_ID(+) AND " +
			" AMD.CLIENT_ID = CLNT.C_CLIENT_ID(+) AND " +
			" AMD.PROCESS_CODE = P.PROCESS_CODE ");
			
			if(StringUtils.isNotBlank(searchCriteria.getStrApplicationId())){
				query.append(" AND AMD.APPLICATION_ID = '" + searchCriteria.getStrApplicationId() + "' ");
			}
			if(StringUtils.isNotBlank(searchCriteria.getStrWFIncidentId())){
				query.append(" AND AMD.WF_INCIDENT_ID = '" + searchCriteria.getStrWFIncidentId() + "' ");
			}
			if(StringUtils.isNotBlank(searchCriteria.getStrClientRefNo())){
				query.append(" AND upper(AMD.CLIENT_REF_NO) like '%" + searchCriteria.getStrClientRefNo().toUpperCase() + "%' ");
			}
			if(StringUtils.isNotBlank(searchCriteria.getStrProcessCode())){
				query.append(" AND upper(AMD.PROCESS_CODE) = '" + searchCriteria.getStrProcessCode().toUpperCase() + "' ");
			}
			if(StringUtils.isNotBlank(searchCriteria.getStrStatusCode())){
				//query.append(" AND upper(AMD.WF_STATUS) = '" + searchCriteria.getStrStatusCode().toUpperCase() + "' ");
				query.append(" AND ( upper(AMD.WF_STATUS_EN) like '%" + searchCriteria.getStrStatusCode().toUpperCase() + "%' ");
				query.append(" OR upper(AMD.WF_STATUS_AR) like '%" + searchCriteria.getStrStatusCode().toUpperCase() + "%' ) ");
			}
			if(StringUtils.isNotBlank(searchCriteria.getStrClientName())){
				query.append(" AND ( upper(NCCR.CC_NAME_ENG) like '%" + searchCriteria.getStrClientName().toUpperCase() + "%' " +
						" OR upper(NCCR.CC_NAME_ARB) like '%" + searchCriteria.getStrClientName().toUpperCase() + "%' " +
						" OR upper(NCPR.CP_NAM_ENG) like '%" + searchCriteria.getStrClientName().toUpperCase() + "%' " +
						" OR upper(NCPR.CP_NAME_ARB) like '%" + searchCriteria.getStrClientName().toUpperCase() + "%' ) ");
			}
			if(StringUtils.isNotBlank(searchCriteria.getStrTrakheesId())){
				query.append(" AND ( upper(CLNT.C_TRAKHEES_ID) = '" + searchCriteria.getStrTrakheesId().toUpperCase() + "' " +
						" OR upper(CAR.U_TRAKHEES_ID) = '" + searchCriteria.getStrTrakheesId().toUpperCase() + "' ) " );
			}
			if(StringUtils.isNotBlank(searchCriteria.getStrLicenseNo())){
				query.append(" AND upper(NCCR.CC_LIC_NO) = '" + searchCriteria.getStrLicenseNo().toUpperCase() + "' ");
			}
			query.append(" order by  AMD.APPLICATION_ID ");
			SQLQuery nativeSQLQuery = getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery(query.toString());
			nativeSQLQuery.addScalar("strApplicationId", StringType.INSTANCE);
			nativeSQLQuery.addScalar("strWFIncidentId", StringType.INSTANCE);
			nativeSQLQuery.addScalar("strClientRefNo", StringType.INSTANCE);
			nativeSQLQuery.addScalar("strProcessCode", StringType.INSTANCE);
			nativeSQLQuery.addScalar("strProcessCodeDescEn", StringType.INSTANCE);
			nativeSQLQuery.addScalar("strProcessCodeDescAr", StringType.INSTANCE);
			nativeSQLQuery.addScalar("strStatusDescEn", StringType.INSTANCE);
			nativeSQLQuery.addScalar("strStatusDescAr", StringType.INSTANCE);
			nativeSQLQuery.addScalar("strApplicationLink", StringType.INSTANCE);
			nativeSQLQuery.setResultTransformer(Transformers.aliasToBean(WorkflowRequestVO.class));
			
			@SuppressWarnings("unchecked")
			List<WorkflowRequestVO> requestList = nativeSQLQuery.list();
		
			return (requestList != null && requestList.size() > 0) ? requestList : null;
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	public List<LabelVO> searchRequestStatus(String searchString) {
		Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(AppMetaData.class, "appMetaData");
			criteria.add(
				Restrictions.disjunction().add(
					Restrictions.ilike("appMetaData.wfStatusEn", "%"+searchString+"%")).
					add(Restrictions.ilike("appMetaData.wfStatusAr", "%"+searchString+"%")));
			
		ProjectionList proList = Projections.projectionList();
			proList.add(Projections.property("appMetaData.wfStatusEn"),  "labelEn");
			proList.add(Projections.property("appMetaData.wfStatusAr"), "labelAr");
		  
		 criteria.setProjection(Projections.distinct(proList));
		  
		// criteria.createAlias("appMetaData.wfStatusEn", "labelEn").createAlias("appMetaData.wfStatusAr", "labelAr");
		 
		 criteria.setResultTransformer(new AliasToBeanResultTransformer (LabelVO.class));
		
		 if(criteria.list() != null && criteria.list().size() > 0){
			
			 return criteria.list();
			 
		 }else{
			 
			 return null;
		 }	 
		
	}
	

/*	public List<AppMetaData> findCompletedAppMetaDataByUserId(AppMetaDataDetailVO appMetaDataDetailVO) {
		// TODO Auto-generated method stub
		Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(AppMetaData.class, "appMetaData");
		criteria.add(Restrictions.eq("appMetaData.userId", appMetaDataDetailVO.getlUserId()));
		criteria.addOrder(Order.desc("appMetaData.applicationDate"));
		criteria.add(Restrictions.ne("wfStatusEn", appMetaDataDetailVO.getStrStatusDescEn()));
		@SuppressWarnings("unchecked")		
		List<AppMetaData> list =  criteria.list();
		
		if( list != null && list.size() > 0)
			return list;
		else
			return null;
	}
*/
/*	public List<AppMetaData> findApplicationRequestsByProcessCode(WFProcessType processType) {
		// TODO Auto-generated method stub
		Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(AppMetaData.class, "appMetaData");
		criteria.createAlias("appMetaData.process", "process");
		criteria.add(Restrictions.eq("process.processCode", processType.toString()));
		criteria.addOrder(Order.desc("appMetaData.applicationDate"));
		@SuppressWarnings("unchecked")		
		List<AppMetaData> list =  criteria.list();
		
		if( list != null && list.size() > 0)
			return list;
		else
			return null;

	}

	public List<AppMetaData> findApplicationRequestsByInitiator(String username) {
		// TODO Auto-generated method stub
		Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(AppMetaData.class, "appMetaData");
		criteria.add(Restrictions.eq("appMetaData.userId", username));
		criteria.addOrder(Order.desc("appMetaData.applicationDate"));
		@SuppressWarnings("unchecked")		
		List<AppMetaData> list =  criteria.list();
		
		if( list != null && list.size() > 0)
			return list;
		else
			return null;

	}

	public List<AppMetaData> findApplicationRequestsByApplicationDate(String date) {
		// TODO Auto-generated method stub
		Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(AppMetaData.class, "appMetaData");
		criteria.add(Restrictions.eq("appMetaData.applicationDate", date));
		criteria.addOrder(Order.desc("appMetaData.applicationDate"));
		@SuppressWarnings("unchecked")		
		List<AppMetaData> list =  criteria.list();
		
		if( list != null && list.size() > 0)
			return list;
		else
			return null;

	}*/

}
